package sqlAndIo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import sqlAndIo.pojo.Book;
import sqlAndIo.dao.BookDao;
import sqlAndIo.util.DBUtil;

public class BookDaoImpl implements BookDao {

	public int addBook(Book b) {
		Connection connection = null;
		String sql = "insert into book(id,name,pubDate,author,price,isNew,published) values(seq_03.nextval,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		int m = 0;
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, b.getName());
			ps.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
			ps.setString(3, b.getAuthor());
			ps.setDouble(4, b.getPrice());
			ps.setBoolean(5, true);
			ps.setBoolean(6, true);
			m = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, ps, connection);
		}
		return m;
	}

	public int deleteBook(Integer id) {
		String sql = "delete from book where id=?";
		PreparedStatement ps = null;
		int m = 0;
		Connection connection = null;
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			m = ps.executeUpdate();
			return m;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, ps, connection);
		}
		return 0;
	}

	public int updateBook(Book b) {
		String sql = "update book set name=?,pubDate=?,author=?,price=?,isNew=?,published=? where id=?";
		PreparedStatement ps = null;
		Connection connection = null;
		int r = 0;
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, b.getName());
			ps.setDate(2, new java.sql.Date(b.getPubDate().getTime()));
			ps.setString(3, b.getAuthor());
			ps.setDouble(4, b.getPrice());
			ps.setBoolean(5, b.isNew());
			ps.setBoolean(6, b.isPublished());
			ps.setInt(7, b.getId());
			r = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(null, ps, connection);
		}
		return r;
	}

	public Book loadBook(Integer id) {
		String sql = "select * from book where id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection connection = null;
		Book book = null;
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				book = new Book();
				book.setId(rs.getInt(1));
				book.setName(rs.getString(2));
				book.setPubDate(new Date(rs.getDate(3).getTime()));
				book.setAuthor(rs.getString(4));
				book.setNew(rs.getBoolean(5));
				book.setPublished(rs.getBoolean(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, connection);
		}
		return book;
	}

	public List<Book> findAll() {
		String sql = "select * from book";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection connection = null;
		Book book = null;
		List<Book> books = new ArrayList<Book>();
		try {
			connection = DBUtil.getConnection();
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				book = new Book();
				book.setId(rs.getInt(1));
				book.setName(rs.getString(2));
				book.setPubDate(new Date(rs.getDate(3).getTime()));
				book.setAuthor(rs.getString(4));
				book.setNew(rs.getBoolean(5));
				book.setPublished(rs.getBoolean(6));
				books.add(book);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, ps, connection);
		}
		return books;
	}
}
